<?php
class Answer extends Model {
	/**
	 * 删除一条视频答案
	 *
	 * @param unknown $id        	
	 * @return resource
	 */
	public function delete($id) {
		$sql = "DELETE FROM `answer` WHERE `answer_id` = $id";
		return $this->del ( $sql );
	}
	
	public function insertEvalue($userId, $answerId) {
		$sql = "INSERT INTO evalue VALUES ($userId, $answerId, 0)";
		return $this->insert ( $sql );
	}
	
	public function enableEvalue($userId, $answerId) {
		$sql = "UPDATE evalue SET evalue = 0 WHERE user_id = $userId AND answer_id = $answerId";
		return $this->update ( $sql );
	}

	public function evalueAnswer($userId, $answerId) {
		$sql = "UPDATE evalue SET evalue = 1 WHERE user_id = $userId AND answer_id = $answerId";
		return $this->update ( $sql );
	}
	
	public function getNotEvalueList($userId, $timeStamp, $pageType, $num) {
		$sql = "SELECT answer.answer_id AS id, 
					answer.answer_title AS title,
					answer.answer_create_time AS time, 
					answer.answer_video_time AS duration,
					IFNULL(AVG(`comment_score`),'暂无评分') AS `videoAverage`, 
					uuser.user_nickname AS author,
					uuser.user_id AS author_id
				FROM answer INNER JOIN evalue ON answer.answer_id = evalue.answer_id
				LEFT JOIN ccomment ON answer.answer_id = ccomment.answer_id
				LEFT JOIN uuser ON answer.answer_create_userid = uuser.user_id
				WHERE evalue = 0 AND evalue.user_id = $userId AND ";
		if ($timeStamp == "null" || $timeStamp == "" || $timeStamp == "NULL") {
		} else if ($pageType == "pre") {
			$sql .= "`answer`.`answer_id` > $timeStamp AND ";
		} else if ($pageType == "more") {
			$sql .= "`answer`.`answer_id` < $timeStamp AND ";
		} else {
			$sql .= "`answer`.`answer_id` > $timeStamp AND ";
		}
		
		$sql .= " 1=1 GROUP BY `answer`.`answer_id` ORDER BY `answer`.`answer_id` DESC LIMIT $num";
// 		echo $sql;
		return $this->fetchAll ( $sql );
	}
	public function scoreAnswer($id, $score, $userId, $type) {
		$sql = "SELECT `answer_judge1_score`, 
					`answer_judge2_score`, 
					`answer_judgefinal_score`
				FROM `answer`
				WHERE `answer_id` = $id";
		$res = $this->fetchRow ( $sql );
		$sql2 = "";
		
		if ($userId == null) {
			$userId = - 1;
		}
		
		if ($res ["answer_judge1_score"] == null && $type == "0") {
			$sql2 .= "UPDATE answer SET answer_judge1_score = $score , answer_state = 4, answer_judge1_userid = $userId WHERE answer_id = $id";
		} else if ($res ["answer_judge2_score"] == null && $type == "0") {
			if ($res ["answer_judge1_score"] < 6 && $score < 6) {
				// 两个均失败，直接转失败
				$sql2 .= "UPDATE answer SET answer_judge2_score = $score , answer_state = 3, answer_judge2_userid = $userId WHERE answer_id = $id";
			} else if ($res ["answer_judge1_score"] > 6 && $score > 6) {
				// 两个均成功，直接转成功
				$sql2 .= "UPDATE answer SET answer_judge2_score = $score , answer_state = 1, answer_judge2_userid = $userId WHERE answer_id = $id";
			} else {
				// 仅有一个人成功，转终审
				$sql2 .= "UPDATE answer SET answer_judge2_score = $score , answer_state = 2, answer_judge2_userid = $userId WHERE answer_id = $id";
			}
		} else if ($res ["answer_judgefinal_score"] == null && $type == "1") {
			if ($score < 6) {
				$sql2 .= "UPDATE answer SET answer_judgefinal_score = $score , answer_state = 3, answer_judgefinal_userid = $userId WHERE answer_id = $id";
			} else {
				$sql2 .= "UPDATE answer SET answer_judgefinal_score = $score , answer_state = 1, answer_judgefinal_userid = $userId WHERE answer_id = $id";
			}
		} else {
			return false;
		}
		// echo $sql2;
		return $this->update ( $sql2 );
	}
	public function getList($userId, $timeStamp, $pageType, $pageSize, $type) {
		$sql = "SELECT `answer`.`answer_id` AS `videoId`, 
					`answer_create_time` AS `videoTime`, 
					`answer_video_time` AS `videoDuration`, 
					`answer_state` AS `videoState`, 
					`answer_video_url` AS `videoUrl`, 
					answer.answer_title AS videoTitle,
					answer.`ex_id` AS `quesId`, 
					`answer`.`answer_id` AS `timeStamp`,
					IFNULL(AVG(`comment_score`),'暂无评分') AS `videoAverage`,
					exercise.ex_title AS exTitle,
					uuser.user_nickname AS authorName
				 
				FROM answer LEFT JOIN uuser ON uuser.user_id = answer.answer_create_userid
					LEFT JOIN exercise ON answer.ex_id = exercise.ex_id
					LEFT JOIN ccomment ON answer.answer_id = ccomment.answer_id 
				WHERE ";
		if ($timeStamp == "null" || $timeStamp == "" || $timeStamp == "NULL") {
		} else if ($pageType == "pre") {
			$sql .= "`answer`.`answer_id` > $timeStamp AND ";
		} else if ($pageType == "more") {
			$sql .= "`answer`.`answer_id` < $timeStamp AND ";
		} else {
			return false;
		}
		
		// 根据userId获取视频
		if ($userId != null && $userId != "" && $userId != "null") {
			$sql .= "`answer_create_userid` = $userId AND ";
		}
		
		// 按类别获取视频list，0-未审核，1-审核通过，2-初审失败，3-终审失败
		if ($type == null || $type == "" || $type == "null") {
		} else if ($type == "0") {
			$sql .= "(`answer_state` = 0 OR `answer_state` = 4) AND ";
		} else if ($type == "1") {
			$sql .= "`answer_state` = 2 AND ";
		}
		
		$sql .= "1=1 GROUP BY `answer`.`answer_id` ORDER BY `answer`.`answer_id` DESC LIMIT $pageSize";
		
// 		echo $sql;
		return $this->fetchAll ( $sql );
	}
	public function searchList($timeStamp, $pageType, $pageSize, $videoState, $beginTime, $endTime, $knowsId, $skillId, $type) {
		$sql = "SELECT `answer`.`answer_id` AS `videoId`,
					answer.answer_create_time AS videoTime,
					answer.answer_video_time AS videoDuration,
					answer.answer_state AS videoState,
					answer.answer_video_url AS videoUrl,
					answer.answer_title AS videoTitle,
					answer.ex_id AS quesId,
					`answer`.`answer_id` AS `timeStamp`,
					IFNULL(AVG(`comment_score`),'暂无评分') AS `videoAverage`,
					exercise.ex_title AS exTitle,
					uuser.user_nickname AS authorName
				FROM answer LEFT JOIN exercise_knowledge ON answer.ex_id = exercise_knowledge.ex_id
					LEFT JOIN exercise_skill ON exercise_knowledge.ex_id = exercise_skill.ex_id
					LEFT JOIN uuser ON uuser.user_id = answer.answer_create_userid
					LEFT JOIN exercise ON answer.ex_id = exercise.ex_id 
					LEFT JOIN ccomment ON answer.answer_id = ccomment.answer_id
				WHERE ";
		if ($timeStamp == "null" || $timeStamp == "" || $timeStamp == "NULL") {
		} else if ($pageType == "pre") {
			$sql .= "`answer`.`answer_id` > $timeStamp AND ";
		} else if ($pageType == "more") {
			$sql .= "`answer`.`answer_id` < $timeStamp AND ";
		} else {
			return false;
		}
		if ($videoState != null && $videoState != - 1) {
			$sql .= "`answer_state` = $videoState AND ";
		}
		if ($beginTime != null && beginTime != - 1) {
			$sql .= "`answer_video_time` > $beginTime AND ";
		}
		if ($endTime != null && endTime != - 1) {
			$sql .= "`answer_video_time` < $endTime AND ";
		}
		if ($knowsId != null && $knowsId != "-1") {
			$knowsIds = split ( "_", $knowsId );
			$sql .= "(";
			for($i = 0; $i < count ( $knowsIds ) - 1; $i ++) {
				$sql .= "`knows_id` = $knowsIds[$i] OR ";
			}
			$sql .= "`knows_id` = " . $knowsIds [count ( $knowsIds ) - 1] . ") AND ";
		}
		if ($skillId != null && $skillId != "-1") {
			$skillIds = split ( "_", $skillId );
			$sql .= "(";
			for($i = 0; $i < count ( $skillIds ) - 1; $i ++) {
				$sql .= "`skill_id` = $skillIds[$i] OR ";
			}
			$sql .= "`skill_id` = " . $skillIds [count ( $skillIds ) - 1] . ") AND ";
		}
		// 按类别获取视频list，0-未审核(或仅初审一次)，1-审核通过，2-初审失败，3-终审失败
		if ($type == null || $type == "" || $type == "null") {
		} else if ($type == "0") {
			$sql .= "(`answer_state` = 0 OR `answer_state` = 4) AND ";
		} else if ($type == "1") {
			$sql .= "`answer_state` = 2 AND ";
		}
		$sql .= "1=1  GROUP BY videoId ORDER BY videoId DESC";
// 		echo $sql;
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 根据习题Id获取视频
	 *
	 * @param unknown_type $exId        	
	 * @return Ambigous <boolean, multitype:>
	 */
	public function getAnswerByExerciseId($exId) {
		$sql = "SELECT `answer`.* FROM `answer` WHERE `answer`.`ex_id` = '" . $exId . "'";
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 获取审核通过的视频答案
	 *
	 * @param unknown_type $exId        	
	 * @return Ambigous <boolean, multitype:>
	 */
	public function getPassAnswerByExerciseId($exId) {
		$sql = "SELECT `answer`.* FROM `answer` " . "WHERE `answer`.`ex_id` = '" . $exId . "' AND `answer`.`answer_state` = '1'";
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 获取视频列表（分页）
	 *
	 * @param unknown_type $state        	
	 * @param unknown_type $subId        	
	 * @param unknown_type $typeId        	
	 * @param unknown_type $page        	
	 * @param unknown_type $num        	
	 * @return multitype:unknown number Ambigous <boolean, multitype:>
	 */
	public function getVideoListPageModel($state, $keywords, $subId = 0, $typeId = 0, $page = 1, $num = 10) {
		$select = "SELECT `answer`.*, `exercise`.`ex_title`, c.`user_realname` AS `create_user_name`, judge1.`user_realname` AS `judge1_uesr_name`, " . "judge2.`user_realname` AS `judge2_user_name`, judgefinal.`user_realname` AS `judgefinal_user_name`, `subject`.`sub_cont`, `exercise_type`.`et_cont`, `picture`.`pic_link` " . "FROM `answer` " . "LEFT JOIN `exercise` ON `answer`.`ex_id` = `exercise`.`ex_id` " . "LEFT JOIN `uuser` c ON `answer`.`answer_create_userid` = c.`user_id` " . "LEFT JOIN `uuser` judge1 ON `answer`.`answer_judge1_userid` = judge1.`user_id` " . "LEFT JOIN `uuser` judge2 ON `answer`.`answer_judge2_userid` = judge2.`user_id` " . "LEFT JOIN `uuser` judgefinal ON `answer`.`answer_judgefinal_userid` = judgefinal.`user_id` " . "LEFT JOIN `subject` ON `exercise`.`sub_id` = `subject`.`sub_id` " . "LEFT JOIN `picture` ON `answer`.`answer_pic_id` = `picture`.`pic_id` " . "LEFT JOIN `exercise_type` ON `exercise`.`et_id` = `exercise_type`.`et_id` ";
		$where = "WHERE 1";
		if ($keywords){
			$where .= " AND `answer`.`answer_title` LIKE '%".$keywords."%'";
		}
		if ($state != 4) {
			$where .= " AND `answer`.`answer_state` = '" . $state . "'";
		}
		if ($subId) {
			$where .= " AND `subject`.`sub_id` = '" . $subId . "'";
		}
		if ($typeId) {
			$where .= " AND `exercise`.`et_id` = '" . $typeId . "'";
		}
		$order = " ORDER BY `answer`.`answer_create_time` DESC";
		$limit = " LIMIT " . ($page - 1) * $num . ", " . $num . "";
		$sql = $select . $where . $order . $limit;
		$sqlTotal = $select . $where . $order;
		//echo $sqlTotal;
		$list = $this->fetchAll ( $sql );
		$total = count ( $this->fetchAll ( $sqlTotal ) );
		$totalPage = ceil ( $total / $num );
		return array (
				'page' => $page,
				'list' => $list,
				'total' => $total,
				'totalPage' => $totalPage 
		);
	}
	
	/**
	 * 获取视频列表
	 *
	 * @param unknown_type $page        	
	 * @param unknown_type $num        	
	 * @return multitype:unknown number Ambigous <boolean, multitype:>
	 */
	public function getVideoList($page = 1, $num = 10) {
		$sql = "SELECT `answer`.*, `exercise`.`ex_title`, c.`user_realname` AS `create_user_name`, judge1.`user_realname` AS `judge1_uesr_name`, " . "judge2.`user_realname` AS `judge2_user_name`, judgefinal.`user_realname` AS `judgefinal_user_name` " . "FROM `answer` " . "LEFT JOIN `exercise` ON `answer`.`ex_id` = `exercise`.`ex_id` " . "LEFT JOIN `uuser` c ON `answer`.`answer_create_userid` = c.`user_id` " . "LEFT JOIN `uuser` judge1 ON `answer`.`answer_judge1_userid` = judge1.`user_id` " . "LEFT JOIN `uuser` judge2 ON `answer`.`answer_judge2_userid` = judge2.`user_id` " . "LEFT JOIN `uuser` judgefinal ON `answer`.`answer_judgefinal_userid` = judgefinal.`user_id` " . "LIMIT " . ($page - 1) * $num . ", " . $num . " ";
		$sqlTotal = "SELECT `answer`.*, `exercise`.`ex_title`, c.`user_realname` AS `create_user_name`, judge1.`user_realname` AS `judge1_uesr_name`, " . "judge2.`user_realname` AS `judge2_user_name`, judgefinal.`user_realname` AS `judgefinal_user_name` " . "FROM `answer` " . "LEFT JOIN `exercise` ON `answer`.`ex_id` = `exercise`.`ex_id` " . "LEFT JOIN `uuser` c ON `answer`.`answer_create_userid` = c.`user_id` " . "LEFT JOIN `uuser` judge1 ON `answer`.`answer_judge1_userid` = judge1.`user_id` " . "LEFT JOIN `uuser` judge2 ON `answer`.`answer_judge2_userid` = judge2.`user_id` " . "LEFT JOIN `uuser` judgefinal ON `answer`.`answer_judgefinal_userid` = judgefinal.`user_id`";
		echo $sqlTotal;
		$list = $this->fetchAll ( $sql );
		$total = count ( $this->fetchAll ( $sqlTotal ) );
		$totalPage = ceil ( $total / $num );
		return array (
				'page' => $page,
				'list' => $list,
				'total' => $total,
				'totalPage' => $totalPage 
		);
	}
	
	/**
	 * 根据Id获取视频答案详情
	 *
	 * @param unknown_type $answerId        	
	 * @return Ambigous <boolean, multitype:>
	 */
	public function getDetailById($answerId) {
		$sql = "SELECT `answer`.*, `exercise`.`ex_title`,c.`user_nickname`, c.`user_realname` AS `create_user_name`, judge1.`user_realname` AS `judge1_uesr_name`, " . "judge2.`user_realname` AS `judge2_user_name`, judgefinal.`user_realname` AS `judgefinal_user_name`, `subject`.`sub_cont`, `exercise_type`.`et_cont` " . "FROM `answer` " . "LEFT JOIN `exercise` ON `answer`.`ex_id` = `exercise`.`ex_id` " . "LEFT JOIN `uuser` c ON `answer`.`answer_create_userid` = c.`user_id` " . "LEFT JOIN `uuser` judge1 ON `answer`.`answer_judge1_userid` = judge1.`user_id` " . "LEFT JOIN `uuser` judge2 ON `answer`.`answer_judge2_userid` = judge2.`user_id` " . "LEFT JOIN `uuser` judgefinal ON `answer`.`answer_judgefinal_userid` = judgefinal.`user_id` " . "LEFT JOIN `subject` ON `exercise`.`sub_id` = `subject`.`sub_id` " . "LEFT JOIN `exercise_type` ON `exercise`.`et_id` = `exercise_type`.`et_id` " . "WHERE `answer`.`answer_id` = '" . $answerId . "'";
		return $this->fetchRow ( $sql );
	}
	
	// public function getCommentPageModel($exId, $page = 1, $num = 10){
	// $sql = "SELECT `answer`.`answer_create_time`,`answer`.`answer_create_userid`, `ccomment`.*,a.`user_realname`, b.`user_realname` AS `comment_to_user_realname` FROM `answer` "
	// . "LEFT JOIN `ccomment` ON `answer`.`answer_id` = `ccomment`.`answer_id` "
	// . "LEFT JOIN `uuser` a ON `ccomment`.`comment_create_userid` = a.`user_id` "
	// . "LEFT JOIN `uuser` b ON `ccomment`.`comment_to_userid` = b.`user_id` "
	// . "WHERE `answer`.`ex_id` = '".$exId."' "
	// . "LIMIT ".($page-1)*$num.", ".$num." ";
	// $sqlTotal = "SELECT `answer`.`answer_create_time`,`answer`.`answer_create_userid`, `ccomment`.*,a.`user_realname`, b.`user_realname` AS `comment_to_user_realname` FROM `answer` "
	// . "LEFT JOIN `ccomment` ON `answer`.`answer_id` = `ccomment`.`answer_id` "
	// . "LEFT JOIN `uuser` a ON `ccomment`.`comment_create_userid` = a.`user_id` "
	// . "LEFT JOIN `uuser` b ON `ccomment`.`comment_to_userid` = b.`user_id` "
	// . "WHERE `ex_id` = '".$exId."' ";
	// echo $sqlTotal;
	// $list = $this->fetchAll($sql);
	// $total = count($this->fetchAll($sqlTotal));
	// $totalPage = ceil($total / $num);
	// return array('page'=>$page,'list'=>$list,'total'=>$total,'totalPage'=>$totalPage);
	// }
	
	/**
	 * 根据题目id获取视频列表
	 *
	 * @param unknown_type $page        	
	 * @param unknown_type $num        	
	 * @return multitype:unknown number Ambigous <boolean, multitype:>
	 */
	public function getVideoListByExId($questionID, $timestamp, $pagetype, $num) {
		// 类别，1-录制审核通过；2-互审成功；3-终审成功；4-兑换现金
		if ($timestamp == "null" || $timestamp == "" || $timestamp == "NULL") {
			$sql = "SELECT `answer`.*, `uuser`.* FROM `answer` 
					LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id` 
					WHERE `answer`.`ex_id`=$questionID 
					ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
		} else {
			if ($pagetype == "pre") {
				$sql = "SELECT `answer`.*, `uuser`.* FROM `answer` 
						LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id` 
						WHERE `answer`.`ex_id`=$questionID  AND `score_id`>$timestamp
						ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
			} else {
				$sql = "SELECT `answer`.*, `uuser`.* FROM `answer` 
						LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id` 
						WHERE `answer`.`ex_id`=$questionID  AND `score_id`<$timestamp
						ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
			}
		}
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 根据题目id 和 题号获取视频列表
	 */
	public function getVideoListByExIdAndNum($questionID, $quesNum, $timestamp, $pagetype, $num) {
		$squesNum = "";
		if ($quesNum) {
			$squesNum = "AND `answer`.`answer_question`=$quesNum ";
		}
		if ($timestamp == "null" || $timestamp == "" || $timestamp == "NULL") {
			$sql = "SELECT `answer`.*, `uuser`.* FROM `answer`
			LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id`
			WHERE `answer`.`ex_id`=$questionID AND `answer`.`answer_state`=1 " . $squesNum . "
			ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
		} else {
			if ($pagetype == "pre") {
				$sql = "SELECT `answer`.*, `uuser`.* FROM `answer`
			LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id`
			WHERE `answer`.`ex_id`=$questionID  AND `answer`.`answer_state`=1 " . $squesNum . "  AND `answer`.`answer_id`>$timestamp
			ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
			} else {
				$sql = "SELECT `answer`.*, `uuser`.* FROM `answer`
			LEFT JOIN `uuser` ON `answer`.`answer_create_userid` = `uuser`.`user_id`
			WHERE `answer`.`ex_id`=$questionID  AND `answer`.`answer_state`=1 " . $squesNum . "  AND `answer`.`answer_id`<$timestamp
			ORDER BY `answer`.`answer_id` DESC LIMIT $num ";
			}
		}
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
	
	/**
	 * 根据题目ID获取可以录制的题目号
	 *
	 * @author linkai
	 * @version 2013-1-8 上午11:26:14
	 *         
	 */
	public function getUsefulID($userID, $questionID) {
		$sql = "SELECT `exercise`.`ex_question_num`,`answer`.`answer_question` FROM `exercise`
			LEFT JOIN `answer` ON `answer`.`ex_id`=`exercise`.`ex_id`
			WHERE `answer`.`answer_create_userid`=$userID AND `exercise`.`ex_id`=$questionID";
		// echo $sql;
		return $this->fetchAll ( $sql );
	}
}